package Dao.Process.Imp;

import Dao.Process.MediumDao;
import Entity.Process.Medium;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MediumDaoImp implements MediumDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;


    @Override
    public Medium getMediumBySNO(String SNO, Connection conn) {
        Medium medium = null;
        try {
            String sql = "select " +
                    "sno, tno, is_one, is_report, is_survey, problem, plan, lag_reason, s_manner, t_manner, quality, opinion, is_pass, state, is_change ,change_reason " +
                    "from medium " +
                    "where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            rs = ps.executeQuery();
            if (rs.next()) {
                String sno = rs.getString(1);
                String tno = rs.getString(2);
                String isOne = rs.getString(3);
                String isReport = rs.getString(4);
                String isSurvey = rs.getString(5);
                String problem = rs.getString(6);
                String plan = rs.getString(7);
                String lagReason = rs.getString(8);
                String sManner = rs.getString(9);
                String tManner = rs.getString(10);
                String quality = rs.getString(11);
                String opinion = rs.getString(12);
                String isPass = rs.getString(13);
                String state = rs.getString(14);
                String isChange = rs.getString(15);
                String changeReason = rs.getString(16);
                medium = new Medium(sno, tno, isChange, changeReason, isOne, isReport, isSurvey, problem, plan, lagReason, sManner, tManner, quality, opinion, isPass, state);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return medium;
    }

    @Override
    public Integer insertMedium(Medium medium, Connection conn) {
        int result = 0;
        try {
            String sql = "insert into " +
                    " medium (sno, tno, is_change, change_reason, is_one, is_report, is_survey, problem, plan, lag_reason, s_manner, t_manner, quality, opinion, is_pass, state) " +
                    "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, medium.getSno());
            ps.setString(2, medium.getTno());
            ps.setString(3, medium.getIsChange());
            ps.setString(4, medium.getChangeReason());
            ps.setString(5, medium.getIsOne());
            ps.setString(6, medium.getIsReport());
            ps.setString(7, medium.getIsSurvey());
            ps.setString(8, medium.getProblem());
            ps.setString(9, medium.getPlan());
            ps.setString(10, medium.getLagReason());
            ps.setString(11, medium.getSManner());
            ps.setString(12, medium.getTManner());
            ps.setString(13, medium.getQuality());
            ps.setString(14, medium.getOpinion());
            ps.setString(15, medium.getIsPass());
            ps.setString(16, medium.getState());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public Integer updateCheckBySNO(String SNO, String result, String opinion, String state, Connection conn) {
        int res = 0;
        try {
            String sql = "update medium set state = ? , opinion = ? , is_pass = ? where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, state);
            ps.setString(2, opinion);
            ps.setString(3, result);
            ps.setString(4, SNO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer getSumByTNO(String tno, Integer type, Connection conn) {
        int res = 0;
        try {
            String sql;
            if (type == 1) {
                sql = "select COUNT(sno) from medium where tno = ? ";
            } else {
                sql = "select COUNT(sno) from medium where tno = ? and is_pass = '通过' ";
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer getSumBySNO(String sno, Connection conn) {
        int res = 0;
        try {
            String sql = "select COUNT(sno) from medium where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, sno);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer updateBySNO(Medium medium, Connection conn) {
        int result = 0;
        try {
            String sql = "update medium set " +
                    "  tno = ?, is_change = ?, change_reason = ?, is_one = ?, is_report = ?, is_survey = ?, problem = ?, plan =?, " +
                    " lag_reason = ?, s_manner = ?, t_manner = ?, quality = ?, opinion = ?, is_pass = ?, state = ? " +
                    "where sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, medium.getTno());
            ps.setString(2, medium.getIsChange());
            ps.setString(3, medium.getChangeReason());
            ps.setString(4, medium.getIsOne());
            ps.setString(5, medium.getIsReport());
            ps.setString(6, medium.getIsSurvey());
            ps.setString(7, medium.getProblem());
            ps.setString(8, medium.getPlan());
            ps.setString(9, medium.getLagReason());
            ps.setString(10, medium.getSManner());
            ps.setString(11, medium.getTManner());
            ps.setString(12, medium.getQuality());
            ps.setString(13, medium.getOpinion());
            ps.setString(14, medium.getIsPass());
            ps.setString(15, medium.getState());
            ps.setString(16, medium.getSno());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }
}
